﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    class SemilateController
    {
        private DBConnector connector;

        public SemilateController()
        {
            connector = new DBConnector();
        }

        public DataTable getData(String Lname, String Fname)
        {
            EmployeeController empControl = new EmployeeController();
            string query = "SELECT dateSemilate as 'Date', hours as 'Hours', FORMAT(charge,2) as 'Amount Charged' from semilate where employeeCode = " + empControl.getEmployeeCode(Fname, Lname) + " ORDER BY dateSemilate DESC";
            if (connector.openConnection())
            {
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, connector.getConnection());
                MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                connector.closeConnection();
                return dataTable;
            }
            else
            {
                return null;
            }
        }

        public bool addSemilate(SemiLate semilate, String Lname, String Fname)
        {
            MiscChargeController miscCharge = new MiscChargeController();
            DateTime date = DateTime.Parse(semilate.getDate());
            int periodID = miscCharge.getPeriodID(date);
            if (periodID == -1)
            {
                MessageBox.Show("Date selected is not found in a period.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            else
            {
                string query = "INSERT INTO semilate (employeeCode, dateSemilate, periodID, hours, charge) VALUES ((SELECT employeeCode from employee WHERE lastName = \'" + Lname + "\' and firstName = \'" + Fname + "\' ), \'" + semilate.getDate() + "\'," + periodID + " , " + semilate.getHours() + ", " + semilate.getCharge() + ");";

                if (connector.openConnection())
                {
                    try
                    {
                        MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                        cmd.ExecuteNonQuery();
                        connector.closeConnection();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("That semi-late charge for that date already exists. Input another date, or use edit to change the record for that date.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return false;
                    }
                }
                else
                    return false;
            }
        }

        public bool deleteRecord(String date, String Lname, String Fname)
        {
            string query = "DELETE FROM semilate WHERE dateSemilate = \'" + date + "\' and employeeCode = (SELECT employeeCode FROM employee WHERE firstName = \'"+Fname+"\' and lastName = \'"+Lname+"\');";
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Selected record cannot be deleted.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            return false;
        }


        public bool editSemilate(SemiLate semilate,String Lname,String Fname, String PreviousDate)
        {
            string query = "UPDATE semilate SET dateSemilate = \'" + semilate.getDate() + "\', `hours`=" + semilate.getHours() + ", charge = " + semilate.getCharge() + " WHERE employeeCode =(SELECT employeeCode FROM employee WHERE lastName= \'" + Lname + "\' and firstName= \'" + Fname + "\') and dateSemilate = \'" + PreviousDate + "\';";

            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = query;
                    cmd.Connection = connector.getConnection();
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("That date already exists. Input another date.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            else
                return false;
        }
    }
}
